﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using HCQ2_IDAL;
using HCQ2_Model;
using HCQ2_Model.VR.Params;
using System.Globalization;
using System.Data;
using HCQ2_Model.WeiXinApiModel.ParamModel;

namespace HCQ2_DAL_MSSQL
{
    public partial class T_VRBoardADDAL: IT_VRBoardADDAL
    {
        /// <summary>
        ///  获取广告列表
        /// </summary>
        /// <param name="paran"></param>
        /// <returns></returns>
        public List<T_VRBoardAD> GetInitTable(BoardQueryParam paran)
        {
            sb?.Clear();
            sb.Append($@"SELECT TOP {paran.rows} * FROM(SELECT ROW_NUMBER() OVER(ORDER BY ad_order,ad_id) AS rowNumber,* FROM dbo.T_VRBoardAD WHERE 1=1 ");
            if (!string.IsNullOrEmpty(paran.keyword))
                sb.Append($" AND (ad_title LIKE '%{paran.keyword}%' OR group_name LIKE '%{paran.keyword}%' OR ad_note LIKE '%{paran.keyword}%')");
            if(paran.proType>0)
                sb.Append($" AND pro_type={paran.proType} ");
            if(!string.IsNullOrEmpty(paran.adType))
                sb.Append($" AND ad_type='{paran.adType}' ");
            if(paran.platType>0)
                sb.Append($" AND platType={paran.platType} ");
            if (paran.adStatus>0)
                sb.Append($" AND ad_status={paran.adStatus} ");
            string inDate = string.Empty, outDate = string.Empty;
            if (!string.IsNullOrEmpty(paran.inDate))
                inDate = DateTime.ParseExact(paran.inDate, "yyyy-MM-dd", CultureInfo.CurrentCulture, DateTimeStyles.None).ToString();
            if (!string.IsNullOrEmpty(paran.outDate))
                outDate = DateTime.ParseExact(paran.outDate, "yyyy-MM-dd", CultureInfo.CurrentCulture, DateTimeStyles.None).ToString();
            if (!string.IsNullOrEmpty(inDate) && !string.IsNullOrEmpty(outDate))
                sb.AppendFormat($" AND start_time>='{inDate}' AND end_time<='{outDate}' ");
            else if (!string.IsNullOrEmpty(inDate) && string.IsNullOrEmpty(outDate))
                sb.AppendFormat($" AND start_time>='{inDate}' ");
            else if (string.IsNullOrEmpty(inDate) && !string.IsNullOrEmpty(outDate))
                sb.AppendFormat($" AND end_time<='{outDate}' ");
            sb.Append($")ad WHERE ad.rowNumber>{(paran.page - 1) * paran.rows} ORDER BY ad.ad_order DESC;");
            DataTable dt = HCQ2_Common.SQL.SqlHelper.ExecuteDataTable(sb.ToString());
            return HCQ2_Common.Data.DataTableHelper.DataTableToIList<T_VRBoardAD>(dt);
        }
        /// <summary>
        ///  获取广告列表数量
        /// </summary>
        /// <param name="paran"></param>
        /// <returns></returns>
        public int GetInitCount(BoardQueryParam paran)
        {
            sb?.Clear();
            sb.Append($@"SELECT COUNT(0)AS num FROM dbo.T_VRBoardAD WHERE 1=1 ");
            if (!string.IsNullOrEmpty(paran.keyword))
                sb.Append($" AND (ad_title LIKE '%{paran.keyword}%' OR group_name LIKE '%{paran.keyword}%' OR ad_note LIKE '%{paran.keyword}%')");
            if (paran.proType > 0)
                sb.Append($" AND pro_type={paran.proType} ");
            if (!string.IsNullOrEmpty(paran.adType))
                sb.Append($" AND ad_type='{paran.adType}' ");
            if (paran.adStatus > 0)
                sb.Append($" AND ad_status={paran.adStatus} ");
            string inDate = string.Empty, outDate = string.Empty;
            if (!string.IsNullOrEmpty(paran.inDate))
                inDate = DateTime.ParseExact(paran.inDate, "yyyy-MM-dd", CultureInfo.CurrentCulture, DateTimeStyles.None).ToString();
            if (!string.IsNullOrEmpty(paran.outDate))
                outDate = DateTime.ParseExact(paran.outDate, "yyyy-MM-dd", CultureInfo.CurrentCulture, DateTimeStyles.None).ToString();
            if (!string.IsNullOrEmpty(inDate) && !string.IsNullOrEmpty(outDate))
                sb.AppendFormat($" AND start_time>='{inDate}' AND end_time<='{outDate}' ");
            else if (!string.IsNullOrEmpty(inDate) && string.IsNullOrEmpty(outDate))
                sb.AppendFormat($" AND start_time>='{inDate}' ");
            else if (string.IsNullOrEmpty(inDate) && !string.IsNullOrEmpty(outDate))
                sb.AppendFormat($" AND end_time<='{outDate}' ");
            return HCQ2_Common.Helper.ToInt(HCQ2_Common.SQL.SqlHelper.ExecuteScalar(sb.ToString()));
        }

        //********************************************头记接口*************************************************
        /// <summary>
        ///  获取广告
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public List<T_VRBoardAD> GetBoardByChart(BoardChartParam model)
        {
            sb?.Clear();
            string str = "";
            if (model.size > 0)
                str = $" TOP {model.size} ";
            sb.Append($@"SELECT {str} * FROM  dbo.T_VRBoardAD WHERE ad_status=2 ");
            if (model.proType > 0)
                sb.Append($" AND pro_type={model.proType} ");
            if (!string.IsNullOrEmpty(model.adType))
                sb.Append($" AND ad_type='{model.adType}' ");
            //店铺id
            if (model.storeId > -1)
                sb.Append($" AND store_id={model.storeId} ");
            //平台类别
            if (model.platType > 0)
                sb.Append($" AND platType={model.platType} ");
            //分组名
            if(!string.IsNullOrEmpty(model.groupName))
                sb.Append($" AND group_name='{model.groupName}' ");
            //过期范围
            if(model.pastType==0)
                //未过期
                sb.Append(" AND start_time<=GETDATE() AND end_time>=GETDATE() ");
            else if (model.pastType == 1)
                //已过期
                sb.Append(" AND end_time<GETDATE() ");
            string tempStr = " ad_order DESC ";
            switch (model.orderType)
            {
                case 1: tempStr = " ad_order ASC "; break;//序号升序
                case 2: tempStr = " update_time DESC "; break;//日期倒序
                case 3: tempStr = " update_time ASC "; break;//日期升序
            }
            sb.Append($" ORDER BY {tempStr};");
            DataTable dt = HCQ2_Common.SQL.SqlHelper.ExecuteDataTable(sb.ToString());
            return HCQ2_Common.Data.DataTableHelper.DataTableToIList<T_VRBoardAD>(dt);
        }
        /// <summary>
        ///  获取关卡单词列表
        /// </summary>
        /// <param name="param"></param>
        /// <returns></returns>
        public List<T_VRCustomPassWord> GetCustomPassWord(RandomWordParam param,int file_id)
        {
            sb?.Clear();
            sb.Append($@"SELECT * FROM dbo.T_VRCustomPassWord WHERE cp_id IN(SELECT cp_id FROM T_VRCustomPass WHERE cp_code='{param.cp_code}' AND file_id={file_id}) AND file_id={file_id};");
            DataTable dt = HCQ2_Common.SQL.SqlHelper.ExecuteDataTable(sb.ToString());
            return HCQ2_Common.Data.DataTableHelper.DataTableToIList<T_VRCustomPassWord>(dt);
        }
        /// <summary>
        ///  获取已购买商品数量
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public int GetPayGoodNum(PayGoodNumParam model,int user_id)
        {
            sb?.Clear();
            sb.Append($@"SELECT COUNT(0) AS num FROM 
            (SELECT order_code FROM dbo.T_VROrder WHERE user_id={user_id} AND status=2)orders INNER JOIN
            (SELECT file_id,order_code FROM dbo.T_VROrderDetail)detial ON orders.order_code=detial.order_code ");
            if (model.pro_type > 0)
                sb.Append($" INNER JOIN (SELECT file_id FROM dbo.T_DocumentInfo WHERE pro_type={model.pro_type})info ON info.file_id = detial.file_id ");
            return HCQ2_Common.Helper.ToInt(HCQ2_Common.SQL.SqlHelper.ExecuteScalar(sb.ToString()));
        }
    }
}
